package ltg.ibutton.hydroroom;

import java.awt.Point;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;

import ltg.ibutton.core.Concept;

import org.jgrapht.alg.DijkstraShortestPath;
import org.jgrapht.graph.DefaultWeightedEdge;
import org.jgrapht.graph.SimpleDirectedWeightedGraph;

/**
 * This is just a "service" class that provides all the storage methods. They
 * are all implemented as static methods. This gives more freedom on which storage
 * systems can be used. In this implementation, both database and simple text files
 * are used to store simulation information.
 * @author Francesco Novellis
 *
 */
public class HydroRoomPersistence {
	private static final String DBUSER = "hydroroom";
	private static final String DBPWD = "hydroroom";
	private static final String DEFAULT_PROBE_CONF = "conf/triggeredprobes.conf";
	private static final String DYES_CONF = "conf/dyes.conf";
	private static final String ROOM_CONF = "conf/classroom.conf";
	private static final String STATION_CONF = "conf/station.conf";
	private static final String COLOREDTILES_CONF = "conf/coloredtiles.conf";
	private static final String AQUIFERS_CONF = "conf/aquifers.conf";
	private static final String FLOWS_CONF = "conf/flows.conf";
	private static final String JDBCSTRING = "jdbc:mysql://192.168.1.2/hydroroom?user="+DBUSER+"&password="+DBPWD;
	
	private static final int TIME_UNIT = 3; //minutes
	
	private static Connection conn = null;
	private static Statement stmt = null;
	private static ResultSet rs = null;
	
	/**
	 * Actually it does not do anything at all
	 */
	private HydroRoomPersistence() {}
	
	/**
	 * In charge of freeing all the variables after a db transaction
	 */
	public static void closeEverything() {
		if (rs != null) {
	        try { rs.close(); } catch (SQLException sqlEx) { }
	        rs = null;
	    }
	    if (stmt != null) {
	        try { stmt.close(); } catch (SQLException sqlEx) { }
	        stmt = null;
	    }
	    if (conn != null) {
	        try { conn.close(); } catch (SQLException sqlEx) { }
	        conn = null;
	    }
	}
	
	/**
	 * In the config file button IDs and correspondent color dye are written one per row.
	 * This method returns a hashtable containing these information.
	 * 
	 * @return Hashtable of buttonID, dye color
	 */
	public static Hashtable<String, String> getDyesConf() {
		Hashtable<String, String> dyes = new Hashtable<String, String>();
		try {
			BufferedReader in = new BufferedReader(new FileReader(DYES_CONF));
			String s;
			while((s = in.readLine())!= null) {
				String[] tmp = s.split(" ", 2);
				dyes.put(tmp[0], tmp[1]);
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return dyes;
	}
	
	/**
	 * The classroom config file contains info about the physical measures of the
	 * classroom and in how many blocks each dimension has to be divided in the grid
	 * map. This method reads those information frome the file and returns them in a
	 * hashtable 
	 * 
	 * @return Hashtable used as associative array for width, height, xblocks and yblocks 
	 * for the grid
	 */
	public static Hashtable<String, Float> getClassroomConf() {
		Hashtable<String, Float> classroom = new Hashtable<String, Float>();
		try {
			BufferedReader in = new BufferedReader(new FileReader(ROOM_CONF));
			String s;
			String[] tmp = new String[6];
			tmp[0] = "width";
			tmp[1] = "height";
			tmp[2] = "xblocks";
			tmp[3] = "yblocks";
			tmp[4] = "xwidth";
			tmp[5] = "ywidth";
			int kk=0;
			while((s = in.readLine())!= null) {
				classroom.put(tmp[kk++], Float.parseFloat(s));
			}
			classroom.put(tmp[kk++], classroom.get("width")/classroom.get("xblocks"));
			classroom.put(tmp[kk], classroom.get("height")/classroom.get("yblocks"));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return classroom;
	}
	
	/**
	 * Reads the configuration file and returns the list of the readers that must NOT
	 * be auto-updating
	 * 
	 * @return ArrayList of the readers that must NOT be auto-updating
	 */
	public static ArrayList<String> getTriggeredProbes() {
		ArrayList<String> probes = new ArrayList<String>();
		try {
			BufferedReader in = new BufferedReader(new FileReader(DEFAULT_PROBE_CONF));
			String s;
			while((s = in.readLine())!= null)
				probes.add(s);
		} 
		catch (FileNotFoundException e) { return probes; } 
		catch (IOException e) { return probes; }
		return probes;
	}
	
	/**
	 * Checks in the database whether the button ID has been already associated
	 * to some concept or not, in order to verify if that button has already a 
	 * meaning, a role in the simulation.
	 * 
	 * @param bid, button ID associated with the physical object
	 * @return Concept object of the correspondent info retrieved
	 * or default one if no info are found
	 * 
	 */
	public static Concept getConceptFromBid(String bid) {
		Concept concept = new WaterSample(bid, getThisStationId());
		if(HydroRoomPersistence.getDyesConf().containsKey(bid)) {
			concept = new DyeTracer(bid, HydroRoomPersistence.getDyesConf().get(bid)); //Reading dye tracers does not 
																					// involve x,y position
		}
		else {
			try {
				conn = DriverManager.getConnection(JDBCSTRING);
				stmt = conn.createStatement();
				rs = stmt.executeQuery("SELECT * FROM simdata WHERE bid = '"+bid+"' AND active='1'");
			
				if(rs.next()) {
					concept = new WaterSample(bid, rs.getTimestamp("timestamp"),
					rs.getFloat("x"),
					rs.getFloat("y"),
					rs.getString("stationid"),
					rs.getString("info"));
				}
			} catch (SQLException ex) {
			    System.err.println("SQLException: " + ex.getMessage());
			    System.err.println("SQLState: " + ex.getSQLState());
			    System.err.println("VendorError: " + ex.getErrorCode());
			} finally {
			    closeEverything();
			}
		}
		return concept;
	}
	
	/**
	 * Checks whether the location has water or not
	 * 
	 * @param float x, x location of the sample
	 * @param float y, y location of the sample
	 * @return Boolean value, true if the location is in an aquifer
	 */
	public static boolean hasWater(int x, int y) {
		System.err.println(x + " " + y);
		try {
			BufferedReader in = new BufferedReader(new FileReader(AQUIFERS_CONF));
			String s = null;
			for(int i=0; i<y; i++)
				s = in.readLine();
			
			if(s != null && s.charAt(x-1) != '0')
				return true;
			else 
				return false;
				
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	/**
	 * Extracts information from the Concept object and stores them into the 
	 * storage system, in this case a database
	 * 
	 * @param Concept cc, Concept object to be stored
	 * @return Boolean value true if the procedure was successful
	 */
	public static boolean storeConcept(Concept cc) {
		String query = null;
		boolean ret = false;
		Hashtable<String, Float> info = getClassroomConf();
		Timestamp t = new Timestamp(Calendar.getInstance().getTimeInMillis());
		
		query = "INSERT INTO simdata VALUES ('";
		if(cc.getClass().getName().equals("ltg.ibutton.hydroroom.InjectedDyeTracer")) {
			query += ((InjectedDyeTracer) cc).getBid()+"', "; //buttonID
			query += "'"+t+"', "; //timestamp
			query += "'"+((InjectedDyeTracer) cc).getX()+"', "; //x
			query += "'"+((InjectedDyeTracer) cc).getY()+"', "; //y
			query += "'"+((InjectedDyeTracer) cc).getStationId()+"', "; //stationid
			query += "'color="+((InjectedDyeTracer) cc).getColor()+"', "; // dye tracer info
			query += "'D', "; //type dye tracer
		}
		else { //empty tube
			query += ((WaterSample) cc).getBid()+"', "; //buttonID
			query += "'"+t+"', "; //timestamp
			query += "'"+((WaterSample) cc).getX()+"', "; //x in meters
			query += "'"+((WaterSample) cc).getY()+"', "; //y in meters
			query += "'"+((WaterSample) cc).getStationId()+"', "; //stationid
			
			int x = (int) Math.ceil(((WaterSample) cc).getX()/info.get("xwidth"));
			int y = (int) Math.ceil(((WaterSample) cc).getY()/info.get("ywidth"));
			if(hasWater(x,y))
				query += "'"+dyeTrace(x, y, t)+"', ";  //water sample info
			else
				query +="'NO WATER', ";
			query += "'S', "; //type sample
		}
		query += "'1')"; //active
		try {
			conn = DriverManager.getConnection(JDBCSTRING);
			stmt = conn.createStatement();			
			ret = stmt.execute(query);
		} catch (SQLException ex) {
			System.err.println("Query: " + query);
		    System.err.println("SQLException: " + ex.getMessage());
		    System.err.println("SQLState: " + ex.getSQLState());
		    System.err.println("VendorError: " + ex.getErrorCode());
		} finally {
			closeEverything();
		}
		return ret;
	}
	
	/**
	 * Checks whether the button has an associated meaning to it, a role into the simulation
	 * 
	 * @param String bid, the unique id of the button in question
	 * @return Boolean value true if the button ID has not an active meaning
	 */
	public static boolean isAnewTube(String bid) {
		boolean ret = false;
		try {
			conn = DriverManager.getConnection(JDBCSTRING);
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT COUNT(*) AS num FROM simdata WHERE bid = '"+bid+"' AND active='1'");
			
			if(rs.next() && (rs.getInt("num") == 0))
				ret = true;
		} catch (SQLException ex) {
		    System.err.println("SQLException: " + ex.getMessage());
		    System.err.println("SQLState: " + ex.getSQLState());
		    System.err.println("VendorError: " + ex.getErrorCode());
		} finally {
			closeEverything();
		}
		return ret;
	}
	
	/**
	 * Retrieve from the database information about the dye tracers injected into the simulation
	 * 
	 * @return ArrayList of the active dye tracers
	 */
	public static ArrayList<InjectedDyeTracer> getInjectedDyes() {
		ArrayList<InjectedDyeTracer> dyes = new ArrayList<InjectedDyeTracer>();
		
		try {
			conn = DriverManager.getConnection(JDBCSTRING);
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM simdata WHERE type = 'D' AND active='1'");
			
			while(rs.next()) {
				dyes.add(new InjectedDyeTracer(
						rs.getString("bid"),
						HydroRoomPersistence.getDyesConf().get(rs.getString("bid")),
						rs.getTimestamp("timestamp"),
						rs.getFloat("x"),rs.getFloat("y"),
						rs.getString("stationid")));
			}
		} catch (SQLException ex) {
		    System.err.println("SQLException: " + ex.getMessage());
		    System.err.println("SQLState: " + ex.getSQLState());
		    System.err.println("VendorError: " + ex.getErrorCode());
		} finally {
			closeEverything();
		}
		
		return dyes;
	}
	
	/**
	 * Reads in the config file the Id of the current station
	 * 
	 * @return String ID of the current station
	 */
	public static String getThisStationId() {
		try {
			BufferedReader in = new BufferedReader(new FileReader(STATION_CONF));
			String s;
			if((s = in.readLine())!= null)
				return s;
			return null;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;		
	}
	
	/**
	 * Reads in the config file of the colored tiles
	 * 
	 * @return ArrayList of the grid coordinates of the tiles to be filled with color
	 */
	public static Hashtable<Point, Integer> getColoredTiles() {
		Hashtable<Point, Integer> tmp = new Hashtable<Point, Integer>();
		Hashtable<String, Float> info = getClassroomConf();
		try {
			BufferedReader in = new BufferedReader(new FileReader(COLOREDTILES_CONF));
			String s;
			for(int i=0; i<info.get("yblocks"); i++) {
				s = in.readLine();
				for(int j=0; j<info.get("xblocks"); j++) {
					if(s.charAt(j) != '0') {
						Character c = new Character(s.charAt(j));
						tmp.put(new Point(j, i), Integer.parseInt(c.toString()));
					}
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return tmp;		
	}
	
	/**
	 * Reads in the config file of the station
	 * 
	 * @return String[3] containing the three values of the RGB color
	 */
	public static String[] getTilesColor() {
		try {
			BufferedReader in = new BufferedReader(new FileReader(STATION_CONF));
			in.readLine();
			String s;
			if((s = in.readLine())!= null) {
				String[] rgb = s.split(" ", 3);
				return rgb;
			}
			return null;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;	
	}
	
	
	/**
	 * Writes in the config file the colored tiles
	 * 
	 * @param Hashtable<Point, Integer> of the points/directions where to draw the colored tiles
	 */
	public static void setColoredTiles(Hashtable<Point, Integer> p) {
		Hashtable<String, Float> info = getClassroomConf();
		try {
			BufferedWriter out = new BufferedWriter(new FileWriter(COLOREDTILES_CONF));
			for(int i=0; i<info.get("yblocks"); i++) {
				for(int j=0; j<info.get("xblocks"); j++) {
					Point pt = new Point(j,i);
					if(p.containsKey(pt))
						out.write(p.get(pt).toString());
					else
						out.write("0");
				}
				//System.err.println(p.get(i).x + " " + p.get(i).y + " " + c.get(i));
				out.newLine();
			}
			out.flush();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}	
	}
	
	
	/**
	 * Retrieves from the database all the logged operation made by the current station
	 * 
	 * @return ResultSet with records of the logged actions of the station
	 */
	public static ResultSet getActionsByStation() {
		String sid = HydroRoomPersistence.getThisStationId();
		try {
			conn = DriverManager.getConnection(JDBCSTRING);
			stmt = conn.createStatement();
			String query = "SELECT type, timestamp, x, y, info FROM simdata WHERE stationid = '"+sid+"' ORDER BY timestamp ASC";
			//System.err.println(query);
			return stmt.executeQuery(query); 
		} catch (SQLException ex) {
		    System.err.println("SQLException: " + ex.getMessage());
		    System.err.println("SQLState: " + ex.getSQLState());
		    System.err.println("VendorError: " + ex.getErrorCode());
		} 
		return rs; //closure need to be called later
	}
	
	/**
	 * Calculates the contribution of each injected dye to the spectrometry of the sample 
	 * taken at location (x,y) at time t
	 * 
	 * @param x, the x coord on the grid
	 * @param y, the y coord on the grid
	 * @return String with the formatted dye information to be stored into the DB
	 */
	public static String dyeTrace(int x, int y, Timestamp t) {
		SimpleDirectedWeightedGraph<String, DefaultWeightedEdge> g = 
			new SimpleDirectedWeightedGraph<String, DefaultWeightedEdge>(DefaultWeightedEdge.class);
		Hashtable<String,Float> info = getClassroomConf();
		String retStr = "";

		/* Creation of the vertices of the flow graph */
		for(int i=0; i<info.get("yblocks"); i++)
			for(int j=0; j<info.get("xblocks"); j++) {
				g.addVertex(String.valueOf((char) (j + (int) 'A')) + (i+1));
			}
		
		/* Creation of the edges of the flow graph */
		try {
			BufferedReader in = new BufferedReader(new FileReader(FLOWS_CONF));
			String s;
			while((s = in.readLine())!= null) {
				String[] tmp = s.split(" ");
				for(int i=0; i<tmp.length; i++) {
					String source = tmp[i].substring(0, tmp[i].indexOf(":"));
					String[] outarcs = tmp[i].substring(tmp[i].indexOf(":")+1,tmp[i].indexOf("@")).split(",");
					String flowrate = tmp[i].substring(tmp[i].indexOf("@")+1);
					for(int j=0; j<outarcs.length; j++) {
						g.addEdge(source, outarcs[j]);
						g.setEdgeWeight(g.getEdge(source, outarcs[j]), Double.parseDouble(flowrate));
					}
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		/* Scanning of the injected dyes and calculation of each contribution */
		Hashtable<String, Double> founddyes = new Hashtable<String,Double>();
		
		for(Iterator<InjectedDyeTracer> i=getInjectedDyes().iterator(); i.hasNext();) {
			InjectedDyeTracer injd = i.next();
			Timestamp t1 = injd.getTimestamp();
			long timediff =t.getTime()-t1.getTime();
			
			if(timediff > (12*60*60*1000)) //To exclude injection older than 12 hours
				continue;
			else {
				String v1 = String.valueOf((char) ((int) Math.floor(injd.getX()/info.get("xwidth")) + (int) 'A')) + 
					((int) Math.ceil(injd.getY()/info.get("ywidth")));
				
				String v2 = String.valueOf((char) (x - 1 + (int) 'A')) + y;
				
				double weight = 100000;
				if(!g.outgoingEdgesOf(v1).isEmpty()) {
					weight = g.getEdgeWeight(g.outgoingEdgesOf(v1).iterator().next());
				}
				System.err.println(weight);
				
				List<DefaultWeightedEdge> path = DijkstraShortestPath.findPathBetween(g, v1, v2);
				if(path != null) {
					System.err.println(v2 + "("+x+","+y+")"+" from " + v1 + ": " + path.size()); 
					System.err.println(path + " " + path.size() + " " + timediff);
					
					if(path.size() == (int) Math.floor(timediff/(weight*TIME_UNIT*60*1000))) {
						System.err.println(injd.getColor() + " nella tile corrente");
						if(!founddyes.containsKey(injd.getColor()) || founddyes.get(injd.getColor()) < 0.9) {
							founddyes.put(injd.getColor(), 0.9);
							continue;
						}
					}
					else if(path.size() + 1 == (int) Math.floor(timediff/(weight*TIME_UNIT*60*1000))) {
						System.err.println(injd.getColor() + "dye nella tile precedente");
						if(!founddyes.containsKey(injd.getColor()) || founddyes.get(injd.getColor()) < 0.7) {
							founddyes.put(injd.getColor(), 0.7);
						}
					}
				}
			}
		}
		
		for(Enumeration<String> e=founddyes.keys(); e.hasMoreElements();) {
			String k = e.nextElement();
			System.err.println(k);
			retStr += k+"="+founddyes.get(k)+"&";
		}
		
        return retStr;
	}
	
	
	public static void writeSpectrometerLog(String bid) {
		try {
			conn = DriverManager.getConnection(JDBCSTRING);
			stmt = conn.createStatement();
			stmt.execute("INSERT INTO spectrometerlog VALUES(" + 
					"'" + (new Timestamp(Calendar.getInstance().getTimeInMillis())) + 
					"', '"+ bid +"')");
		} catch (SQLException ex) {
		    System.err.println("SQLException: " + ex.getMessage());
		    System.err.println("SQLState: " + ex.getSQLState());
		    System.err.println("VendorError: " + ex.getErrorCode());
		} finally {
			closeEverything();
		}
	}

}
